<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>一起学Excel</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta property="og:type" content="website">
<meta property="og:title" content="一起学Excel">
<meta property="og:url" content="https://lecoder.gitee.io/study-excel/page/65/index.html">
<meta property="og:site_name" content="一起学Excel">
<meta property="og:locale" content="zh_CN">
<meta property="article:author" content="lijile">
<meta name="twitter:card" content="summary">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  
<link rel="stylesheet" href="/study-excel/css/style.css">

<meta name="generator" content="Hexo 6.3.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/study-excel/" id="logo">一起学Excel</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/study-excel/">Home</a>
        
          <a class="main-nav-link" href="/study-excel/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
        <a id="nav-search-btn" class="nav-icon" title="搜索"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="https://lecoder.gitee.io/study-excel"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main">
  
    <article id="post-vba-program-structure" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-program-structure/" class="article-date">
  <time datetime="2022-11-23T08:00:48.494Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-program-structure/">VBA 程序结构入门</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>从本篇开始，我们进入 VBA 语法的另外一个重要概念，程序结构。</p>
<p>上一章学习的<a href="/study-excel/vba-variable/">变量</a>、<a href="/study-excel/vba-data-type/">数据类型</a>、<a href="/study-excel/vba-operators/">运算符</a>等概念，是 VBA 中<code>&quot;静态&quot;</code>的部分。相对的，程序结构是 VBA 中 <code>&quot;动态&quot;</code> 的部分。学习完程序结构，才能真正写出可以运行的 VBA 程序。</p>
<p>本篇我们先介绍程序结构最常用的部分，深入学习前有一个基本概念。本章余下的教程中，将详细介绍每一个程序结构。</p>
<h2 id="程序结构示例"><a href="#程序结构示例" class="headerlink" title="程序结构示例"></a>程序结构示例</h2><p>首先，本篇将使用以下代码，介绍各种程序结构，大家可以先看一下。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    &#x27;声明循环变量和是否为空变量</span><br><span class="line">    Dim i As Integer</span><br><span class="line">    Dim isBlank As Boolean</span><br><span class="line">    </span><br><span class="line">    &#x27;循环 A2-A10 单元格</span><br><span class="line">    For i = 2 To 10</span><br><span class="line">    </span><br><span class="line">        &#x27;存储单元格是否为空的结果</span><br><span class="line">        isBlank = Cells(i, 1).Value = &quot;&quot;</span><br><span class="line">        </span><br><span class="line">        &#x27;如果为空，则用上方的单元格的值填充当前单元格</span><br><span class="line">        If isBlank Then</span><br><span class="line">            Cells(i, 1) = Cells(i - 1, 1)</span><br><span class="line">        End If</span><br><span class="line">        </span><br><span class="line">    Next i</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>以上代码运行后，在 A2:A10 单元格区域，依次判断每一个单元格是否为空，如果是空，则用上一个单元格的值填充。</p>
<h2 id="过程"><a href="#过程" class="headerlink" title="过程"></a>过程</h2><p>过程是 VBA 中，程序实际运行的最小结构。单独的一行或多行代码无法运行，必须把它们放置在一个过程里，才能运行。</p>
<p>在示例中，<code>Sub 过程名()</code> 开头，<code>End Sub</code> 为结尾部分是一个过程的主体，其余代码需要放置在两者之间。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<h2 id="程序语句"><a href="#程序语句" class="headerlink" title="程序语句"></a>程序语句</h2><p>语句，是表示一个完整意思的一行代码。</p>
<p>例如，示例中第一行，声明变量就是一条语句。它表示，声明一个整型变量。同理，第二行、第三行和其余的每一行都是语句。VBA 中的过程，就是由这样一条条语句构成的。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Dim i As Integer</span><br><span class="line">Dim isBlank As Boolean</span><br></pre></td></tr></table></figure>


<blockquote>
<p>通常，一行就是一个语句，除非它用换行符或合并符号，具体可以阅读「<a href="/study-excel/vba-operators/">VBA 运算符入门</a>」。</p>
</blockquote>
<h2 id="Excel-VBA-对象"><a href="#Excel-VBA-对象" class="headerlink" title="Excel VBA 对象"></a>Excel VBA 对象</h2><p>我们学习 VBA 的最终目的是操纵 Excel，完成一些特定的目标。其中，操纵 Excel 就是通过 Excel VBA 对象完成的。</p>
<p>在示例中，<code>Cells()</code> 就是一个 Excel VBA 对象，表示一个单元格，提供行号和列号指定单元格。</p>
<h2 id="程序运行结构"><a href="#程序运行结构" class="headerlink" title="程序运行结构"></a>程序运行结构</h2><p>接下来是程序结构中最核心的部分，也是最有意思的部分，<strong>程序运行结构</strong>。大部分编程语言都具备基本的三种程序运行结构，分别是顺序结构、循环结构、判断结构。各种简单到复杂的算法，都是由这三种基本的结构，相互组合而完成。</p>
<h3 id="1-顺序结构"><a href="#1-顺序结构" class="headerlink" title="1.顺序结构"></a>1.顺序结构</h3><p>首先是基本的顺序结构。顺序结构，顾名思义就是按照顺序依次执行。VBA 中的顺序就是从上到下、从左到右的顺序。</p>
<p>在示例中，首先运行两个声明语句，然后运行循环结构，以此类推。值得注意的事，当程序有嵌套时，嵌套的部分也是按照顺序指定的。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_c9f83fde893232b4488c93f810dfdd8a.png"></p>
<h3 id="2-循环结构"><a href="#2-循环结构" class="headerlink" title="2.循环结构"></a>2.循环结构</h3><p>第二个基本结构是循环结构。当使用循环结构时，循环部分代码，按照指定的循环次数，循环重复执行。</p>
<p>在我们的示例中，For 至 Next i 之间的代码就是一个循环代码。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_a69682c8f18b379a024988f4eae0cd01.png"></p>
<p>VBA 中，有多种循环结构，本例中是 For 循环结构。For 循环结构中，第一行指定循环次数，最后一行表示开始下一个循环。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">&#x27;循环开始</span><br><span class="line">For i = 2 To 10</span><br><span class="line">	&#x27;这里是循环的代码</span><br><span class="line">Next i</span><br></pre></td></tr></table></figure>

<h3 id="3-判断结构"><a href="#3-判断结构" class="headerlink" title="3.判断结构"></a>3.判断结构</h3><p>最后一种基本结构是，判断结构。简单来说，该结构中，当提供的表达式为真(True)时，判断结构的主体部分才会被执行，否则跳过。</p>
<p>在示例代码中，If 开头和 End If 结尾处是典型的判断结构。第一行，判断 isBlank 变量是否为真，如果是，则执行判断结构主体部分，否则跳过。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">&#x27;如果为空，则用上方的单元格的值填充当前单元格</span><br><span class="line">If isBlank Then</span><br><span class="line">    Cells(i, 1) = Cells(i - 1, 1)</span><br><span class="line">End If</span><br></pre></td></tr></table></figure>

<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_68bcc1187e95478ea0e87ec18ea436fc.png"></p>
<h2 id="注释"><a href="#注释" class="headerlink" title="注释"></a>注释</h2><p>通常，一段代码写完后，不会完全没问题。在实际使用过程中可能需要修改，符合最新的需求。过一段时间再打开查看时，可能已经忘记了当时的思路，不能很快想起来有些代码实际的用途，更不用说让别人查看了。这时，就需要<strong>注释</strong>出场了。</p>
<p>注释是对代码的一种解释，不影响代码的运行。VBA 中的注释语句是，以英文单引号 <code>(&#39;)</code>开头，后接需要解释说明的内容。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_aa170b064a51108d86f26a4ba6aae105.png"></p>
<p>注释可以让代码更容易理解，建议从一开始就养成写注释的习惯。</p>
<h2 id="运行"><a href="#运行" class="headerlink" title="运行"></a>运行</h2><p>下面，我们实际运行我们的示例代码，看一看它具体的效果。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_7b2593af26d4405393748c4586c6e50c.gif"></p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>现在你学会了基本的 VBA 程序结构。可以运行的最小单位是 VBA 过程，一个 VBA 过程由一个或多个语句组成，每一个语句表示一个完整的含义。VBA 是按顺序结构执行，遇到循环结构和判断结构时，根据提供的参数循环重复执行，或跳过某一段代码。</p>
<p>本章余下教程，将详细介绍以上每一部分。学完本章后，你就可以写出一个完整可运行的 VBA 程序了。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-program-structure/" data-id="clatcwdzw00aexcq6538z49tl" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-expression-statement" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-expression-statement/" class="article-date">
  <time datetime="2022-11-23T08:00:48.494Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-expression-statement/">VBA 表达式和语句</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>在昨天的教程中说到，一行代码就是一条<a href="/study-excel/vba-program-structure/">语句</a>。那么，VBA 中的语句具体是什么，语句还包括哪些基本的结构，语句和表达式有什么关系，今天的教程中，给大家详细介绍。</p>
<h2 id="表达式"><a href="#表达式" class="headerlink" title="表达式"></a>表达式</h2><p>首先，相比语句更小的一种代码结构是，<strong>表达式</strong>。按照字面意思，可以分为两部分，表达和式。组合起来是，通过一种式子，表达相应的值。</p>
<p>VBA 中，返回一个值的代码片段，称之为表达式。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_5a4378a031f49c9195567410188d5cf0.png"></p>
<p>最简单的，最基本的数据写出来就是一个表达式，它返回的是自己本身（100、”Excel” 等）。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">100 &#x27;=&gt; 100</span><br><span class="line">&quot;Hello World&quot; &#x27;=&gt; Hello World</span><br><span class="line">True &#x27;=&gt; True</span><br><span class="line">#2018-11-1# &#x27;=&gt;2018-11-1</span><br></pre></td></tr></table></figure>

<p>表达式也可以是，多个数据之间的运算表达式。例如，算数运算、逻辑运算、连接运算等。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">1+2 &#x27;=&gt; 3</span><br><span class="line">10 &gt; 5 &#x27;=&gt; True</span><br><span class="line">&quot;Hello &quot; &amp; &quot;World&quot; &#x27;=&gt; Hello World</span><br></pre></td></tr></table></figure>

<p>进一步，表达式可以是多个表达式之间的运算。这种时候，往往会把多个表达式用<code>()</code>括起来，保证运算顺序。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Not (10 &gt; 5) &#x27;=&gt; False</span><br><span class="line">(1 + 2) - (10 - 5) &#x27;=&gt; -2</span><br></pre></td></tr></table></figure>

<p>使用 VBA 函数也可以是表达式，例如 <code>Left</code> 函数返回左侧指定长度文本。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Left(&quot;Hello World&quot;, 5) &#x27;=&gt; Hello</span><br><span class="line">Abs(-10) &#x27;=&gt; 10</span><br><span class="line">Year(#2018-11-1#) &#x27;=&gt; 2018</span><br></pre></td></tr></table></figure>

<p>当然，最重要的，表达式中能使用变量。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">Dim place As String</span><br><span class="line">place = &quot;World&quot;</span><br><span class="line"></span><br><span class="line">&quot;Hello&quot; &amp; place &#x27;=&gt; Hello World</span><br><span class="line">Left(place, 3) &#x27;=&gt; Wor</span><br></pre></td></tr></table></figure>

<p> </p>
<h2 id="语句"><a href="#语句" class="headerlink" title="语句"></a>语句</h2><p>编程中光有表达式没有办法做成什么事，需要把表达式与其他元素组合成一个语句，才能发挥它的作用。</p>
<p>VBA 中，一条语句是一个完整指令的。它可以包含关键词、运算符、变量、常量以及表达式。</p>
<p>语句有三大类，分别是：</p>
<ul>
<li><strong>声明式语句</strong>，也就是声明变量、常量、过程或者函数。</li>
<li><strong>执行式语句</strong>，执行指定动作。动作可以包括执行一个过程、开始一个循环、判断表达式等。</li>
<li><strong>赋值语句</strong>，给变量赋值，是执行式语句的特殊形式。</li>
</ul>
<p>接下来，分别介绍三类语句。</p>
<h3 id="1-声明式语句"><a href="#1-声明式语句" class="headerlink" title="1.声明式语句"></a>1.声明式语句</h3><p>在之前的多篇文章，大家已经看到了很多声明式语句。声明式语句用来声明一个新的变量（包括类型），过程、函数、对象、数组等。</p>
<p>我们看以下代码。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line">    Dim name As String</span><br><span class="line">    Dim age As Integer</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>这段代码中，第一个语句就是第一行，声明一个 VBA 过程。声明过程以 <code>Sub</code>开头，后接<code>过程名()</code>。对应的，最后一行是，过程的结束语句，即 <code>End Sub</code></p>
<p>接下来是，两个声明变量的语句，<code>name</code> 和 <code>age</code>，并且语句中指定了他们的类型。</p>
<h3 id="2-执行式语句"><a href="#2-执行式语句" class="headerlink" title="2.执行式语句"></a>2.执行式语句</h3><p>执行式语句执行一个动作。这个动作可以是运行过程、开始循环、判断表达式等。</p>
<p>我们以一个判断语句为例，看一下代码。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line">    </span><br><span class="line">    Dim name As String</span><br><span class="line">    Dim age As Integer</span><br><span class="line">    </span><br><span class="line">    name = &quot;Zhang San&quot;</span><br><span class="line">    age = 28</span><br><span class="line">    </span><br><span class="line">    If age &gt; 25 then</span><br><span class="line">        MsgBox name &amp; &quot; 岁数超过25了。&quot;</span><br><span class="line">    End If</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>这段代码中，<code>If</code> 语句判断 <code>age</code> 变量是否大于 25，如果是，则运行与 <code>End If</code>之间的代码，否则跳过不运行。这其中的<code>If ... End If</code> 就是一个执行式语句。</p>
<p>这种执行式语句是 VBA 语法中的最重要的部分，在接下来的学习中，将详细介绍。</p>
<h3 id="3-赋值语句"><a href="#3-赋值语句" class="headerlink" title="3.赋值语句"></a>3.赋值语句</h3><p>赋值语句，顾名思义给变量赋值。它是执行式语句的一种特殊形式。</p>
<p>赋值语句的核心是 <code>=</code>，左侧是变量，右侧是赋的值。</p>
<p>我还是看上述代码，这次我们加上赋值语句。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line">    </span><br><span class="line">    Dim name As String</span><br><span class="line">    Dim age As Integer</span><br><span class="line">    </span><br><span class="line">    name = &quot;Zhang San&quot;</span><br><span class="line">    age = 28</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>显然，<code>name = &quot;Zhang San&quot;</code>和<code>age = 28</code>是赋值语句。</p>
<p>另外值得注意的是，VBA 中的<a href="/study-excel/vba-variable/">变量</a>可以重复赋值，其值等于最后一次赋的值。这在实际的 VBA 开发中十分有用。</p>
<p> </p>
<h2 id="书写语句的-2-个技巧"><a href="#书写语句的-2-个技巧" class="headerlink" title="书写语句的 2 个技巧"></a>书写语句的 2 个技巧</h2><p>在运算符一文中，简单说了 2 个运算符，一个是英文冒号**(:)<strong>，一个是下划线</strong>(_)**。</p>
<table>
<thead>
<tr>
<th>运算符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>: ( 英文冒号)</td>
<td>将两行代码放置在一行</td>
</tr>
<tr>
<td>_ (下划线)</td>
<td>将一行代码分解成两行</td>
</tr>
</tbody></table>
<h3 id="英文冒号"><a href="#英文冒号" class="headerlink" title=": ( 英文冒号)"></a>: ( 英文冒号)</h3><p>通常，VBA 中直接将多个语句放置在一行，VBA 会报错。如果想要放置在一行，需要用 <code>: ( 英文冒号)</code>连接多个语句。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line">    Dim name As String : Dim age As Integer</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<h3 id="下划线"><a href="#下划线" class="headerlink" title="_ (下划线)"></a>_ (下划线)</h3><p>相反，当一条语句过长时，可能需要分成多行书写。如果把一条语句直接回车分成多行，VBA 也会报错。这时需要把<code>空格 + _</code>放置在换行处。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line">    </span><br><span class="line">    Dim name As String</span><br><span class="line">    </span><br><span class="line">    name = &quot;Hello&quot; &amp; _</span><br><span class="line">    	&quot;World&quot;</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p> </p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>表达式是返回一个值的式子，语句是一个完整的指令。语句包括以下三类：</p>
<ul>
<li>声明式语句</li>
<li>执行式语句</li>
<li>赋值语句</li>
</ul>
<p>VBA 中利用表达式和语句，以多种形式组合使用，做到复杂的计算和操作。下一篇将介绍声明和赋值。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-expression-statement/" data-id="clatcwdzz00aixcq6g01telji" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-variable-declaration" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-variable-declaration/" class="article-date">
  <time datetime="2022-11-23T08:00:48.494Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-variable-declaration/">VBA 变量的声明和赋值</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>在之前的两篇教程中，简单介绍了 VBA 中变量的声明和赋值以及<a href="/study-excel/vba-expression-statement/">基本语句</a>。变量的声明和赋值，通常是一个 VBA 过程的开始，也是最基本的部分。VBA 开发过程中，好的声明方式和正确的赋值过程，是写出准确又高效代码的根本。</p>
<p>本篇教程给大家介绍什么是 VBA 中的声明以及基本语法，然后介绍多个数据类型的赋值方法。学习完本篇，可以开始尝试写一些基本的 VBA 代码了。</p>
<h2 id="什么是声明变量"><a href="#什么是声明变量" class="headerlink" title="什么是声明变量"></a>什么是声明变量</h2><p>在《<a href="/study-excel/vba-variable/">VBA 变量</a>》一文中介绍，变量是一个存储数据的 VBA 代码结构。在代码中，通过变量名获取变量所代表的值。而声明变量，就是告诉 VBA，变量的名字和它所存储的值的数据类型。</p>
<p>VBA 中声明变量，有 4 种变量，它们是：</p>
<ul>
<li><strong>基本类型变量</strong>。基本类型变量是那些存储单个数据的变量，例如数字、文本、日期等。</li>
<li><strong>通用变量</strong>。通用变量，即 Variant 类型变量。该变量的类型在程序运行过程中，根据赋值的数据自动指定。</li>
<li><strong>数组</strong>。数组包含多个变量的集合。</li>
<li><strong>对象</strong>。对象包含一些列属性和方法。</li>
</ul>
<h2 id="声明语句的基本语法"><a href="#声明语句的基本语法" class="headerlink" title="声明语句的基本语法"></a>声明语句的基本语法</h2><p>4 种类型的变量的声明方法基本一致。他们的语法如下。</p>
<h3 id="1-基本类型变量"><a href="#1-基本类型变量" class="headerlink" title="1.基本类型变量"></a>1.基本类型变量</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">&#x27;语法</span><br><span class="line">Dim [变量名] As [数据类型]</span><br><span class="line"></span><br><span class="line">&#x27;实例</span><br><span class="line">Dim name As String</span><br><span class="line">Dim age As Integer</span><br><span class="line">Dim height As Double</span><br><span class="line">Dim birthday As Date</span><br></pre></td></tr></table></figure>

<h3 id="2-通用变量"><a href="#2-通用变量" class="headerlink" title="2.通用变量"></a>2.通用变量</h3><p>声明 Variant 类型变量时，如果忽略数据类型，默认情况是 Variant 类型，因此下方两种方式是相同的。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">&#x27;语法</span><br><span class="line">Dim [变量名] As Variant</span><br><span class="line">Dim [变量名]</span><br><span class="line"></span><br><span class="line">&#x27;实例</span><br><span class="line">Dim message As Variant</span><br><span class="line">Dim message</span><br></pre></td></tr></table></figure>

<h3 id="3-数组"><a href="#3-数组" class="headerlink" title="3.数组"></a>3.数组</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">&#x27;语法</span><br><span class="line">&#x27;固定长度数组声明</span><br><span class="line">Dim [变量名](开始序号 to 结束序号) As [数据类型]</span><br><span class="line">&#x27;动态数组声明</span><br><span class="line">Dim [变量名]() As [数据类型]</span><br><span class="line"></span><br><span class="line">&#x27;实例</span><br><span class="line">&#x27;声明包含10个文本类型元素的数组</span><br><span class="line">Dim names(1 to 10) As String</span><br><span class="line">&#x27;声明长度未知的文本类型数组</span><br><span class="line">Dim names() As String</span><br></pre></td></tr></table></figure>

<p>关于数组的声明和赋值，将在数组一章中做详细的介绍。</p>
<h3 id="4-对象"><a href="#4-对象" class="headerlink" title="4.对象"></a>4.对象</h3><p>声明对象时，一般有两种方式。一种是前期绑定，即一开始就指定对象的类型；一种是后期绑定，即声明时不指定对象类型，后期指定。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">&#x27;语法</span><br><span class="line">&#x27;前期绑定声明语法</span><br><span class="line">Dim [变量名] As [对象类型]</span><br><span class="line">&#x27;后期绑定声明语法</span><br><span class="line">Dim [变量名] As Object</span><br><span class="line"></span><br><span class="line">&#x27;实例</span><br><span class="line">Dim sh As Worksheet</span><br><span class="line">Dim car As Object</span><br></pre></td></tr></table></figure>

<p>关于对象的声明和赋值，在对象一章中做更详细的介绍。</p>
<h2 id="在哪里写声明语句"><a href="#在哪里写声明语句" class="headerlink" title="在哪里写声明语句"></a>在哪里写声明语句</h2><p>声明变量，意思是在使用变量前，告诉 VBA，变量的名字和数据类型。因此，声明变量的语句，必须写在使用它的语句前。</p>
<h3 id="错误写法"><a href="#错误写法" class="headerlink" title="错误写法"></a>错误写法</h3><p>如果先于声明语句前使用变量，VBA 会报「变量未定义」错误。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_ec52b7c4ee7e84ef070d1641e73a38ca.png"></p>
<h3 id="正确写法"><a href="#正确写法" class="headerlink" title="正确写法"></a>正确写法</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Dim name As String</span><br><span class="line">name = &quot;Zhang San&quot;</span><br></pre></td></tr></table></figure>

<h2 id="如何声明多个同类型变量"><a href="#如何声明多个同类型变量" class="headerlink" title="如何声明多个同类型变量"></a>如何声明多个同类型变量</h2><p>通过以上部分的学习，在写多个同类型变量的声明语句时，有人可能会按以下方式写：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">&#x27;声明两个整数类型的 i、j 变量</span><br><span class="line">Dim i,j As Integer</span><br></pre></td></tr></table></figure>

<p>首先，以上写法，语法上没问题，不会出现错误。但是，这种方式声明变量，Integer 类型只作为第二个 j 变量的数据类型。第一个变量，即 i 变量，它的数据类型时 Variant，并不是 Integer 类型。</p>
<p>因此，VBA 中不能合并声明语句。正确的声明方法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">&#x27;第一种，按两行写</span><br><span class="line">Dim i As Integer</span><br><span class="line">Dim j As Integer</span><br><span class="line"></span><br><span class="line">&#x27;第二种，使用 : 符号，在一行写</span><br><span class="line">Dim i As Integer : Dim j As Integer</span><br></pre></td></tr></table></figure>

<h2 id="声明变量是必须的吗"><a href="#声明变量是必须的吗" class="headerlink" title="声明变量是必须的吗"></a>声明变量是必须的吗</h2><p>准确来讲，VBA 中声明变量不是必须的。也就是说，没有声明变量，而直接开始用，也没有错误。</p>
<p>但是，不声明变量，是一种不好的习惯，也常常会带来很多错误。这也是为什么之前的教程中都没有提到这点的原因。</p>
<p>不声明变量典型弊端包括：</p>
<ul>
<li>数据类型自动设置为 Variant 类型，效率低。</li>
<li>变量名写错，不会提示错误。</li>
<li>无法使用 VBA 代码自动补全。</li>
<li>数据类型不匹配时，不会提示错误。</li>
</ul>
<p><strong>基于以上原因，强烈建议，每次使用变量，都要声明其变量名和数据类型。</strong></p>
<p>VBA 提供一个选项，可以强制变量声明，即在模块头部写上以下语句：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Option Explicit</span><br></pre></td></tr></table></figure>

<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_c529f42e94fd74ac8f9d415f81adc7d9.png"></p>
<p>模块中有以上语句时，如果未声明变量而直接使用变量，VBA 会提示「<strong>变量未定义</strong>」错误，方便检查代码。</p>
<p>设置 VBA 编辑器变量声明选项时，可以自动为每个模块插入<code>Option Explicit</code>语句，不需要手动书写。设置方法如下：</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_40574203a51d12b0696f4eccdd517262.png"></p>
<p> </p>
<h2 id="基本类型变量的赋值"><a href="#基本类型变量的赋值" class="headerlink" title="基本类型变量的赋值"></a>基本类型变量的赋值</h2><p>基本类型变量是存储单个值的变量，例如数字、文本、日期等。</p>
<p>VBA 中，给基本类型变量赋值，以 <code>Let</code>关键词开头。赋值操作是给<code>=</code>左侧的变量，用<code>=</code>右边的数据，指定其代表的值。在后续的代码中，该变量就代表指定的数据。</p>
<p><strong>在实际开发中，给基本类型变量赋值时，<code>Let</code>关键词可以忽略不写，直接以变量开头写赋值语句。</strong></p>
<p>给基本类型变量赋值语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">&#x27;语法，两种写法相同</span><br><span class="line">Let [变量名] = [数据]</span><br><span class="line">[变量名] = [数据]</span><br><span class="line"></span><br><span class="line">&#x27;实例</span><br><span class="line">Dim name As String</span><br><span class="line">Let name = &quot;Zhang San&quot;</span><br><span class="line"></span><br><span class="line">Dim age As Integer</span><br><span class="line">Let age = 30</span><br><span class="line"></span><br><span class="line">Dim birthday As Date</span><br><span class="line">Let birthday = #2000-1-1#</span><br></pre></td></tr></table></figure>

<p>赋值语句中，<code>=</code>右侧可以是包含其他变量、函数、复杂计算的表达式。该表达式返回的值的类型，与变量类型一致就可以正常赋值。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">Dim birthday As Date</span><br><span class="line">Dim age As Integer</span><br><span class="line"></span><br><span class="line">birthday = #2000-1-1#</span><br><span class="line">age = Year(Now) - Year(birthday)</span><br></pre></td></tr></table></figure>

<p>一种特殊情况是，<code>=</code>右侧可以是变量本身。这种方式多用于循环结构中。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">Dim i As Integer</span><br><span class="line"></span><br><span class="line">Do While i &lt; 10</span><br><span class="line">    </span><br><span class="line">    Msgbox i</span><br><span class="line">	i = i + 1</span><br><span class="line">    </span><br><span class="line">Loop</span><br><span class="line"></span><br><span class="line">&#x27;返回 =&gt; 0,1,2,3,4,5,6,7,8,9</span><br></pre></td></tr></table></figure>

<h2 id="Variant-类型变量的赋值"><a href="#Variant-类型变量的赋值" class="headerlink" title="Variant 类型变量的赋值"></a>Variant 类型变量的赋值</h2><p>我们知道 Variant 类型时通用类型，因此赋值很直接，没有类型强制要求。Variant 类型变量第一次赋值后，可以继续赋值其他类型数据。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">&#x27;声明变量（两者相同）</span><br><span class="line">Dim message As Variant</span><br><span class="line">Dim message</span><br><span class="line"></span><br><span class="line">&#x27;赋值</span><br><span class="line">message = &quot;Hello World&quot;</span><br><span class="line">message = 1234567890</span><br><span class="line">message = #2018-12-1#</span><br></pre></td></tr></table></figure>

<p><strong>这里依然强调</strong>，虽然 Variant 类型变量比较灵活，但是也有很多弊端，所以在实际开发中，不建议使用该类型，使用确切类型变量。</p>
<p> </p>
<h2 id="数组类型变量的赋值"><a href="#数组类型变量的赋值" class="headerlink" title="数组类型变量的赋值"></a>数组类型变量的赋值</h2><p>数组是可以存储多个同类型元素的数据类型。声明时一般指定其数据长度。给数组赋值时，一般使用每个元素的序号。</p>
<p>数组赋值基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[数组名](元素序号) = [数据]</span><br></pre></td></tr></table></figure>

<p>下面看一下实际的实例。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">&#x27;声明数组</span><br><span class="line">Dim arr(1 to 5) As String</span><br><span class="line">&#x27;数组赋值</span><br><span class="line">arr(1) = &quot;Zhang San&quot;</span><br><span class="line">arr(2) = &quot;Li Si&quot;</span><br><span class="line">arr(3) = &quot;Wang Wu&quot;</span><br></pre></td></tr></table></figure>

<p>以上是数组的基本赋值方式，关于数组的更多声明和赋值方法，将在《数组》一章中详细介绍。</p>
<p> </p>
<h2 id="对象类型变量的赋值"><a href="#对象类型变量的赋值" class="headerlink" title="对象类型变量的赋值"></a>对象类型变量的赋值</h2><p>VBA 中，对象是程序的一个元素，不同于基本类型数据，它包括多个属性和多个方法。例如，Excel 中<a href="/study-excel/set-excel-new-sheet-options/">工作簿</a>、<a href="/study-excel/set-current-workbook-worksheet-display/">工作表</a>、<a href="/study-excel/excel-cell-line-break/">单元格</a>、图表等都是对象。</p>
<p>对象类型变量赋值时，不同于基本类型变量使用<code>Let</code>（可以忽略）关键词，<strong>对象使用 <code>Set</code> 关键词，并且<code>Set</code>关键词不能省略。</strong></p>
<p>如下是对象类型变量基本的赋值方法：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Set [变量名] = [对象类型数据]</span><br></pre></td></tr></table></figure>

<p>下面看一下实际的用法。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">&#x27;声明工作表类型的对象</span><br><span class="line">Dim sheet As Worksheet</span><br><span class="line">&#x27;将名称为“绩效表”的工作表，赋到 sheet 变量</span><br><span class="line">Set sheet = Worksheets(&quot;绩效表&quot;)</span><br></pre></td></tr></table></figure>

<p>由于对象可以包含多个属性，因此 VBA 提供一种同时给多个属性赋值的简单方法。具体方法是对象多个属性赋值语句，放置在 <code>With</code>+<code>对象</code>和<code>End With</code>关键词中间。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">Dim sheet As Worksheet</span><br><span class="line">Set sheet = Worksheets(&quot;绩效表&quot;)</span><br><span class="line"></span><br><span class="line">With sheet</span><br><span class="line">    .Name = &quot;旧绩效&quot;</span><br><span class="line">    .Visible = False</span><br><span class="line">End With</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>关于对象的更多声明和赋值方法，将在《对象》一章中详细介绍。</p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>以上是 VBA 中最基本的变量声明和赋值方法。声明和赋值变量，是代码的开始部分，需要熟练掌握。</p>
<p>不同类型变量，声明和赋值方法做了简单的介绍，除了以上介绍的，不同类型的变量有自己特点，这些部分会在以后的教程详细介绍。</p>
<p>关于变量声明和赋值，还有几个比较重要的知识点，包括相关错误和局部、全局变量，这部分内容将会做单独的、详细的介绍。</p>
<p>大家如果有问题，欢迎留言或私信，大家一起交流探讨。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-variable-declaration/" data-id="clatcwe0200amxcq61tlr03y8" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-program-basic-structure" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-program-basic-structure/" class="article-date">
  <time datetime="2022-11-23T08:00:48.478Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-program-basic-structure/">VBA 程序顺序结构</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>在《<a href="/study-excel/vba-program-structure/">VBA 程序结构入门</a>》一文中说到，程序结构中最核心的部分，<strong>程序运行结构</strong>。</p>
<p>大部分编程语言都具备基本的三种程序运行结构，分别是顺序结构、循环结构、判断结构。各种简单到复杂的算法，都是由这三种基本的结构，相互组合而完成。</p>
<p>今天就介绍其中最基本的顺序结构。</p>
<h2 id="顺序结构执行方式"><a href="#顺序结构执行方式" class="headerlink" title="顺序结构执行方式"></a>顺序结构执行方式</h2><p>顺序结构，顾名思义就是按照一定的顺序依次执行。VBA 中的执行顺序就是，从左到右、从上到下的顺序。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_b72d2087edd899e5c4203a9d05a81797.png"></p>
<p>如下代码运行后，按照上述顺序，依次打印出对应的内容。其中，<code>Debug.Print</code>函数是 VBA 内置函数，用于在立即窗口打印显示指定的内容。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_f3ac52489dafb81f6982be006de4f504.png"></p>
<p> </p>
<h2 id="嵌套过程和函数执行方式"><a href="#嵌套过程和函数执行方式" class="headerlink" title="嵌套过程和函数执行方式"></a>嵌套过程和函数执行方式</h2><p>实际开发中，为了组织代码，会出现在一个过程中，使用其他过程或函数的情形。这种情形与 Excel 中嵌套函数类似。</p>
<p>程序运行到子过程或函数时，按照顺序执行原则，先运行子过程或函数，再继续运行剩余的代码。子过程或函数的执行顺序，依然是顺序执行。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_bc9db3db0c84b528fe9f5d090234382f.png"></p>
<p>如下代码运行后，按照上述顺序，依次打印出对应的内容。其中 <code>Call</code>关键词用于执行一个过程或函数。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_ec0cd3cb91fe82b9501f62a528eb07a9.png"></p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>顺序结构是几种执行方式中最基本的一个。在子过程、子函数、其他执行结构内部，都会按顺序结构执行。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-program-basic-structure/" data-id="clatcwb7r004gxcq6hqq57glh" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-goto-structure" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-goto-structure/" class="article-date">
  <time datetime="2022-11-23T08:00:48.478Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-goto-structure/">VBA GoTo 结构</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>VBA 中，在程序运行时，使用 GoTo 结构，跳转到指定标签处运行，从而不执行 GoTo 语句和指定标签之间的代码。</p>
<p>在实际的 VBA 开发中，Goto 结构多用于处理错误的情形，即发生错误时，跳转到处理错误的代码处。</p>
<h2 id="语法"><a href="#语法" class="headerlink" title="语法"></a>语法</h2><p>GoTo 结构由 GoTo 语句和标签语句组成。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">GoTo [标签]</span><br><span class="line">&#x27;被跳过的代码</span><br><span class="line">...</span><br><span class="line">[标签]:</span><br><span class="line">&#x27;被执行的代码</span><br></pre></td></tr></table></figure>

<p>跳转的位置由 <code>Goto</code> 关键词后的 <code>[标签]</code> 告诉程序，VBA 会在代码中查找对应的 <code>[标签]:</code> 关键词，从标签下一行继续执行程序。</p>
<p>需要注意的是，跳转处的标签，后接冒号 ( : ) 。</p>
<h2 id="实例"><a href="#实例" class="headerlink" title="实例"></a>实例</h2><p>下面的例子说明 GoTo 结构的基本用法。使用 VBA 作除法，如果除数是零，则跳转到程序末尾，提示除数不符合规范。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim num1 As Double</span><br><span class="line">    Dim num2 As Double</span><br><span class="line">    Dim result As Double</span><br><span class="line">    </span><br><span class="line">    num1 = 100</span><br><span class="line">    num2 = 0</span><br><span class="line">    </span><br><span class="line">    If num2 = 0 Then GoTo error</span><br><span class="line">    </span><br><span class="line">    result = num1 / num2</span><br><span class="line">    </span><br><span class="line">    Exit Sub</span><br><span class="line">    </span><br><span class="line">error:</span><br><span class="line">    MsgBox &quot;除数不能为零&quot;</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>可以看到，例子中 <code>num2</code> 是除数，程序判断 <code>num2</code> 是否为零，如果是，则跳转到 <code>Error</code> 标签处，除法部分不会被执行。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_44aa7a24d3bb1d5921d5ab352e0814ac.png"></p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>Goto 结构相对简单，实际开发中不像三大结构使用频繁，但是在一些情形中使用，可以使代码更简洁易读。GoTo 语句常用于错误处理外，还可以用在「上一步&#x2F;下一步」类情形中，根据用户需求，重复执行同一个步骤，这方面内容在以后做更多介绍。</p>
<p>大家有什么疑问，可以留言或私信咨询。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-goto-structure/" data-id="clatcwb83004kxcq6aqfi0e5a" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-program-select-structure" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-program-select-structure/" class="article-date">
  <time datetime="2022-11-23T08:00:48.478Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-program-select-structure/">VBA 程序选择结构</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>VBA 程序执行<a href="/study-excel/vba-program-structure/">三大结构中</a>，选择结构（判断）用于选择性地执行代码。选择结构与 Excel 的 <a href="/study-excel/excel-if-function/"><code>IF 函数</code></a>类似，也是以 <code>IF</code> 为关键词，按照判断条件的真假，执行不同的操作。但是 VBA 中的 <code>IF</code>比 Excel 中的 <code>IF 函数</code>更强大。</p>
<p>今天的文章，我们将学习 VBA 中选择结构的基本语法，并且结合实际的例子，介绍多种选择结构的形式。通过今天的学习，你将学会以下几种选择结构的语法和使用方式：</p>
<ul>
<li>If … Then 结构</li>
<li>If … Else 结构</li>
<li>If ElseIf Else 结构</li>
<li>Select Case 结构</li>
</ul>
<h2 id="选择结构基础"><a href="#选择结构基础" class="headerlink" title="选择结构基础"></a>选择结构基础</h2><p>选择结构，根据提供的条件表达式的值，如果为真（True），则执行选择结构的主体代码，否则跳过。</p>
<p>选择结构的核心是<strong>判断条件表达式的真假</strong>，这一步理解了，也就理解了选择结构的多种形式。</p>
<p>选择结构的基本执行方式如下：</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_991a918a452013f4c5336c0677d591d6.png"></p>
<p> </p>
<h2 id="示例数据"><a href="#示例数据" class="headerlink" title="示例数据"></a>示例数据</h2><p>本篇我们使用一个班级的考试成绩作为示例数据。代码中如何与 Excel 中的数据交互，不需要看懂，这部分在 Excel 对象模型中详细介绍。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_884006130e89bb774e8fc3e2841bd4e0.png"></p>
<p> </p>
<h2 id="If-Then-结构"><a href="#If-Then-结构" class="headerlink" title="If Then 结构"></a>If Then 结构</h2><p>选择结构中，<code>If Then</code> 结构是最基础的一个。它只有条件表达式真时，执行的代码。</p>
<p><code>If Then</code>结构基本语法如下，其中 <code>End If</code>是选择结构的结束标志。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">If 条件表达式 Then</span><br><span class="line">    &#x27;表达式为真时，执行的代码</span><br><span class="line">End If</span><br></pre></td></tr></table></figure>

<p>现在我们看实际的例子，判断学生是否及格，及格条件是成绩 ≥60。如果及格，在C列对应单元格填写“及格”。具体代码如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim i As Integer</span><br><span class="line">    </span><br><span class="line">    For i = 2 To 10</span><br><span class="line">    </span><br><span class="line">        If Cells(i, &quot;B&quot;).Value &gt;= 60 Then</span><br><span class="line">            Cells(i, &quot;C&quot;) = &quot;及格&quot;</span><br><span class="line">        End If</span><br><span class="line">        </span><br><span class="line">    Next i</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>我们可以看到，我们使用 B 列中的学生成绩与 60 分比较，如果≥60分，就在 C 列填写及格。</p>
<p>条件表达式是 <code>Cells(i, &quot;B&quot;).Value &gt;= 60</code>，选择性执行的代码部分是 <code>Cells(i, &quot;C&quot;) = &quot;及格&quot;</code>。</p>
<p>其中，For 语句是表示循环结构，这里只需知道程序从第一个学生循环到最后一个学生，依次判断每个学生的成绩。循环结构将在下一篇中做详细介绍。</p>
<p>将以上代码运行后，可以看到运行结果如下：</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_57bbfcae5eeae4a37ca68d82b27608cf.png"></p>
<p> </p>
<h2 id="If-Else-结构"><a href="#If-Else-结构" class="headerlink" title="If Else 结构"></a>If Else 结构</h2><p>很多时候，我们根据表达式的真假，真时执行一块代码，假时执行另一块代码。这种需求可以使用 <code>If Else</code>结构实现。</p>
<p><code>If Else</code>结构中，条件表达式在真时，执行<code>Then</code>后的代码；条件表达式为假时，执行 <code>Else</code>后的代码。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">If 条件表达式 Then</span><br><span class="line">    &#x27;真时执行的代码</span><br><span class="line">Else</span><br><span class="line">    &#x27;假时执行的代码</span><br><span class="line">End If</span><br></pre></td></tr></table></figure>

<p>我们继续看实际的例子。在上一个例子的基础上，这次对不及格的学生，在C列填入不及格。代码如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim i As Integer</span><br><span class="line">    </span><br><span class="line">    For i = 2 To 10</span><br><span class="line">    </span><br><span class="line">        If Cells(i, &quot;B&quot;).Value &gt;= 60 Then</span><br><span class="line">            Cells(i, &quot;C&quot;) = &quot;及格&quot;</span><br><span class="line">        Else</span><br><span class="line">            Cells(i, &quot;C&quot;) = &quot;不及格&quot;</span><br><span class="line">        End If</span><br><span class="line">        </span><br><span class="line">    Next i</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>在这个例子中，条件表达式 <code>Cells(i, &quot;B&quot;).Value &gt;= 60</code>为假时，表示学生成绩低于60分，即不及格。这时就执行 <code>Else</code>后的代码。</p>
<p>程序运行结果如下：</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_7b501c614cfde6db15462c4928009487.png"></p>
<p> </p>
<h2 id="If-ElseIf-Else-结构"><a href="#If-ElseIf-Else-结构" class="headerlink" title="If ElseIf Else 结构"></a>If ElseIf Else 结构</h2><p>前面两种结构中，最多有两种选择，即 ≥ 60 和 ＜ 60。有时针对同一个变量，可能存在多种判断标准。例如，对及格的学生，继续评级及格、良和优。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_73eb8fec7ea1c9b2f3dc8f39a3e449a5.png"></p>
<p>选择结构中，可以使用 <code>If ElseIf Else</code>结构，对同一个变量进行多次判断，并且为每一个判断结果编写不同的代码块，达到执行式 n 选 1 的效果。</p>
<p><code>If ElseIf Else</code>结构的基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">If 条件表达式1 Then</span><br><span class="line">    &#x27;表达式1真时，执行的代码</span><br><span class="line">ElseIf 条件表达式2 Then</span><br><span class="line">    &#x27;表达式2真时，执行的代码</span><br><span class="line">ElseIf 条件表达式3 Then</span><br><span class="line">    &#x27;表达式3真时，执行的代码</span><br><span class="line">    ...</span><br><span class="line">ElseIf 条件表达式n Then</span><br><span class="line">    &#x27;表达式n真时，执行的代码</span><br><span class="line">Else</span><br><span class="line">    &#x27;以上表达式都不为真时，执行的代码</span><br><span class="line">End If</span><br></pre></td></tr></table></figure>

<p>这种选择结构需要注意的是：</p>
<ul>
<li>条件表达式是从第一个开始判断。</li>
<li>判断过程中，只要有一个表达式结果为真，那么执行对应的代码块，然后退出选择结构，不再继续判断剩下的表达式。</li>
<li>当所有的表达式都不为真时，执行 <code>Else</code>后的代码块。</li>
</ul>
<p>根据以上规律，我们写一下判断学生成绩评级的代码。思路是，拿学生成绩，分别于85、75、60分比较，在 D 列填写对应的评级。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim i As Integer</span><br><span class="line">    </span><br><span class="line">    For i = 2 To 10</span><br><span class="line">    </span><br><span class="line">        If Cells(i, &quot;B&quot;).Value &gt;= 85 Then</span><br><span class="line">            Cells(i, &quot;D&quot;) = &quot;优&quot;</span><br><span class="line">        ElseIf Cells(i, &quot;B&quot;).Value &gt;= 75 Then</span><br><span class="line">            Cells(i, &quot;D&quot;) = &quot;良&quot;</span><br><span class="line">        ElseIf Cells(i, &quot;B&quot;).Value &gt;= 60 Then</span><br><span class="line">            Cells(i, &quot;D&quot;) = &quot;及格&quot;</span><br><span class="line">        Else</span><br><span class="line">            Cells(i, &quot;D&quot;) = &quot;不及格&quot;</span><br><span class="line">        End If</span><br><span class="line">        </span><br><span class="line">    Next i</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>代码运行结果如下：</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_21d521bbab0314d826d821eafe5d2c9d.png"></p>
<p> </p>
<h2 id="Select-Case-结构"><a href="#Select-Case-结构" class="headerlink" title="Select Case 结构"></a>Select Case 结构</h2><p><code>Select Case</code>结构是对同一个变量进行多次判断的另一种方式。相对于<code>If ElseIf Else</code>结构，它把条件表达式中的变量提取出来，使得代码结构更简洁，也更易于阅读。</p>
<p><code>Select Case</code>结构的基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">Select Case 变量</span><br><span class="line">	Case 判断条件 1</span><br><span class="line">    	&#x27;条件 1 真时，执行的代码</span><br><span class="line">	Case 判断条件 2</span><br><span class="line">    	&#x27;条件 2 真时，执行的代码</span><br><span class="line">	Case 判断条件 3</span><br><span class="line">    	&#x27;条件 3 真时，执行的代码</span><br><span class="line">    Case Else</span><br><span class="line">    	&#x27;之前的所有条件都不为真时，执行的代码</span><br><span class="line">End Select</span><br></pre></td></tr></table></figure>

<p>可以看到，<code>Select Case</code>结构把 <code>If</code>结构中的条件表达式拆分了，即把变量和判断条件分开了。</p>
<p>我们看前一个例子，使用<code>Select Case</code>结构，代码如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim i As Integer</span><br><span class="line">    </span><br><span class="line">    For i = 2 To 10</span><br><span class="line">    </span><br><span class="line">        Select Case Cells(i, &quot;B&quot;).Value</span><br><span class="line">            Case Is &gt;= 85</span><br><span class="line">                Cells(i, &quot;D&quot;) = &quot;优&quot;</span><br><span class="line">            Case Is &gt;= 75</span><br><span class="line">                Cells(i, &quot;D&quot;) = &quot;良&quot;</span><br><span class="line">            Case Is &gt;= 60</span><br><span class="line">                Cells(i, &quot;D&quot;) = &quot;及格&quot;</span><br><span class="line">            Case Else</span><br><span class="line">                Cells(i, &quot;D&quot;) = &quot;不及格&quot;</span><br><span class="line">        End Select</span><br><span class="line">        </span><br><span class="line">    Next i</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>这一例子中，学生成绩是变量，即 <code>Cells(i, &quot;B&quot;).Value</code>，判断条件是每个 <code>Case</code> 语句后的条件。</p>
<p>代码运行结构如下：</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_a9b25389efb47260066971686e56a07c.png"></p>
<p> </p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>以上就是选择结构的基本用法，以及 4 种选择结构。选择结构的核心是判断条件表达式的真假，从另一个角度看，核心是如何写条件表达式。这一步写好了，下一步就是根据判断结果执行不同的代码块。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-program-select-structure/" data-id="clatcwb86004mxcq62u7r1pil" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-program-loop-structure" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-program-loop-structure/" class="article-date">
  <time datetime="2022-11-23T08:00:48.478Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-program-loop-structure/">VBA 程序循环结构</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>VBA 中，循环结构用于多次重复执行同一段代码。重复次数通过特定数字或特定条件控制。</p>
<p>通过控制循环过程中特定变量，循环结构可执行复杂的重复任务。</p>
<p>循环结构的执行流程可简单的表示如下：</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_90353b6d2ad2793c8b8d523b2001a647.png"></p>
<h2 id="VBA-循环结构类型"><a href="#VBA-循环结构类型" class="headerlink" title="VBA 循环结构类型"></a>VBA 循环结构类型</h2><p>VBA 中循环结构有 3 种类型，它们是：</p>
<ul>
<li>For 循环</li>
<li>Do While 循环</li>
<li>Do Until 循环</li>
</ul>
<p>下面分别介绍 3 种类型循环结构语法以及用法。</p>
<h2 id="For-循环"><a href="#For-循环" class="headerlink" title="For 循环"></a>For 循环</h2><p>For 循环是最常用的循环类型，它有两种形式：</p>
<ul>
<li>For … Next 循环</li>
<li>For Each 循环</li>
</ul>
<h3 id="For-…-Next-循环"><a href="#For-…-Next-循环" class="headerlink" title="For … Next 循环"></a>For … Next 循环</h3><p>使用 <code>For ... Next</code> 循环可以按指定次数，循环执行一段代码。For 循环使用一个数字变量，从初始值开始，每循环一次，变量值增加或减小，直到变量的值等于指定的结束值时，循环结束。</p>
<p><code>For ... Next</code> 循环语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">For [变量] = [初始值] To [结束值] Step [步长]</span><br><span class="line">    &#x27;这里是循环执行的语句</span><br><span class="line">Next</span><br></pre></td></tr></table></figure>

<p>其中：</p>
<ul>
<li><code>[变量]</code> 是一个数字类型变量，可在循环执行的语句里使用。</li>
<li><code>[初始值]</code> 和 <code>[结束值]</code> 是给定的值；</li>
<li><code>[步长]</code> 是每次循环时，变量的增量。如果为正值，变量增大；如果为负值，变量减小。</li>
</ul>
<p>下面看一个实际的例子，求 1 至 10 数字的累积和。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim i As Integer</span><br><span class="line">    Dim sum As Integer</span><br><span class="line">    </span><br><span class="line">    For i = 1 To 10 Step 1</span><br><span class="line">        sum = sum + i</span><br><span class="line">    Next</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>可以看到，For 循环使用 <code>i</code> 变量，循环 10 次，<code>i</code> 的值从 1 到 10 变化。</p>
<p>值得注意的是，<strong>For 循环的 Step 值如果是 1，则 Step 关键词可省略。</strong>上述过程循环部分可写成如下方式：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">For i = 1 To 10</span><br><span class="line">    sum = sum + i</span><br><span class="line">Next</span><br></pre></td></tr></table></figure>

<h3 id="For-Each-循环"><a href="#For-Each-循环" class="headerlink" title="For Each 循环"></a>For Each 循环</h3><p><code>For Each</code> 循环用于逐一遍历一个数据集合中的所有元素。数据集合包括数组、Excel 对象集合、字典等。</p>
<p>For Each 循环不需要一个数字变量，但是需要与数据集合中的元素相同的数据类型变量。其基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">For Each [元素] In [元素集合]</span><br><span class="line">    &#x27;循环执行的代码</span><br><span class="line">Next [元素]</span><br></pre></td></tr></table></figure>

<p>其中，</p>
<ul>
<li><code>[元素]</code> 是与集合中的元素相同类型的变量，该变量可在循环代码中使用。</li>
<li><code>[元素集合]</code>是包括多个元素的集合。</li>
</ul>
<p>下面看一个实际例子，循环打印出工作簿中所有工作表的名称。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim sh As Worksheet</span><br><span class="line">    </span><br><span class="line">    For Each sh In Worksheets</span><br><span class="line">        Debug.Print sh.Name</span><br><span class="line">    Next sh</span><br><span class="line"></span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p><code>sh</code> 变量就是元素变量，<code>Worksheets</code> 是工作簿中所有工作表的集合。</p>
<h3 id="Exit-For-语句"><a href="#Exit-For-语句" class="headerlink" title="Exit For 语句"></a>Exit For 语句</h3><p><code>Exit For</code> 语句用于跳出循环过程，一般在提前结束循环时使用，均适用于 <code>For Next</code> 循环和 <code>For Each</code> 循环。</p>
<p>看一个实际的例子，求 1 – 10 数字的和时，当和大于 30 就停止循环。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim i As Integer</span><br><span class="line">    Dim sum As Integer</span><br><span class="line">    </span><br><span class="line">    For i = 1 To 10</span><br><span class="line">    </span><br><span class="line">        sum = sum + i</span><br><span class="line">        </span><br><span class="line">        If sum &gt; 30 Then</span><br><span class="line">            Exit For</span><br><span class="line">        End If</span><br><span class="line">        </span><br><span class="line">    Next</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>在这段代码中，<code>sum</code> 变量大于 30 时，循环就停止。</p>
<h2 id="Do-While-循环"><a href="#Do-While-循环" class="headerlink" title="Do While 循环"></a>Do While 循环</h2><p><code>Do While</code> 循环用于满足指定条件时循环执行一段代码的情形。循环的指定条件在 <code>While</code> 关键词后书写。</p>
<p><code>Do While</code> 循环也有两种形式：</p>
<ul>
<li>Do While … Loop 循环</li>
<li>Do … Loop While 循环</li>
</ul>
<h3 id="Do-While-…-Loop-循环"><a href="#Do-While-…-Loop-循环" class="headerlink" title="Do While … Loop 循环"></a>Do While … Loop 循环</h3><p>Do While … Loop 循环，根据 While 关键词后的条件表达式的值，真时执行，假时停止执行。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Do While [条件表达式]</span><br><span class="line">    &#x27;循环执行的代码</span><br><span class="line">Loop</span><br></pre></td></tr></table></figure>

<p>其中，只要 <code>[条件表达式]</code> 为真，将一直循环执行。<code>[条件表达式]</code> 一旦为假，则停止循环，程序执行 <code>Loop</code> 关键词后的代码。</p>
<p>看一个实际的例子，还是求 1- 10 累积和。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Dim i As Integer</span><br><span class="line">    Dim sum As Integer</span><br><span class="line">    </span><br><span class="line">    i = 1</span><br><span class="line">    Do While i &lt;= 10</span><br><span class="line">        sum = sum + i</span><br><span class="line">        i = i + 1</span><br><span class="line">    Loop</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p><code>i</code> 变量的初始值是 1，根据 <code>While</code> 后的条件，只要 <code>i</code> 变量小于等于 10，后续的代码就可以一直循环执行。</p>
<p>这里为了演示使用了 <code>Do While</code> 循环，实际情况下，这种求和问题，使用 <code>For</code> 循环更简洁。</p>
<h3 id="Do-…-Loop-While-循环"><a href="#Do-…-Loop-While-循环" class="headerlink" title="Do … Loop While 循环"></a>Do … Loop While 循环</h3><p>与上一种 Do 循环不同的是，<code>Do ... Loop While</code>循环至少循环执行代码一次后，再判断条件表达式的值。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Do</span><br><span class="line">    &#x27;循环执行的代码</span><br><span class="line">Loop While [条件表达式]</span><br></pre></td></tr></table></figure>

<p>其中，While 和条件表达式写在 Loop 关键词后。</p>
<h3 id="Exit-Do-语句"><a href="#Exit-Do-语句" class="headerlink" title="Exit Do 语句"></a>Exit Do 语句</h3><p>与 <code>Exit For</code> 语句类似，<code>Exit Do</code> 语句用于跳出 <code>Do While</code> 循环。</p>
<h2 id="Do-Until-循环"><a href="#Do-Until-循环" class="headerlink" title="Do Until 循环"></a>Do Until 循环</h2><p><code>Do Until</code> 循环与 <code>Do While</code> 循环类似。不同点在于，<code>Do While</code> 在条件表达式为真时，继续执行循环；而 <code>Do Until</code> 在条件表达式为真时，停止执行循环。</p>
<p>Do Until 循环也有两种形式：</p>
<ul>
<li>Do Until … Loop 循环</li>
<li>Do … Loop Until 循环</li>
</ul>
<h3 id="Do-Until-…-Loop-循环"><a href="#Do-Until-…-Loop-循环" class="headerlink" title="Do Until … Loop 循环"></a>Do Until … Loop 循环</h3><p>循环开始前判断 <code>Until</code> 后条件表达式的值，如果是真，停止循环；如果是假，继续执行循环。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Do Until [条件表达式]</span><br><span class="line">    &#x27;循环执行的代码</span><br><span class="line">Loop</span><br></pre></td></tr></table></figure>

<h3 id="Do-…-Loop-Until-循环"><a href="#Do-…-Loop-Until-循环" class="headerlink" title="Do … Loop Until 循环"></a>Do … Loop Until 循环</h3><p>先运行一次，再判断 <code>Until</code> 后条件表达式的值，如果是真，停止循环；如果是假，继续执行循环。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Do</span><br><span class="line">    &#x27;循环执行的代码</span><br><span class="line">Loop Until [条件表达式]</span><br></pre></td></tr></table></figure>

<p>其他使用方法与 <code>Do While</code> 循环一致。</p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>本篇文章我们学习了 VBA 中程序循环结构基础，以及多种循环结构形式。包括子类在内，VBA 中常使用的循环结构包括 6 种，它们是：</p>
<table>
<thead>
<tr>
<th>循环结构</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>For … Next 循环</td>
<td>按指定次数循环执行</td>
</tr>
<tr>
<td>For Each 循环</td>
<td>逐一遍历数据集合中的每一个元素</td>
</tr>
<tr>
<td>Do While … Loop 循环</td>
<td>当条件为真时，循环执行</td>
</tr>
<tr>
<td>Do … Loop While 循环</td>
<td>当条件为真时，循环执行。无论条件真假，至少运行一次</td>
</tr>
<tr>
<td>Do Until … Loop 循环</td>
<td>直到条件为真时，停止执行</td>
</tr>
<tr>
<td>Do … Loop Until 循环</td>
<td>直到条件为真时，停止执行。无论条件真假，至少运行一次</td>
</tr>
</tbody></table>
<p>此外，学习了两种跳出循环的语句，它们是：</p>
<table>
<thead>
<tr>
<th>跳出语句</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>Exit For</td>
<td>跳出 For 循环</td>
</tr>
<tr>
<td>Exit Do</td>
<td>跳出 Do While&#x2F;Until 循环</td>
</tr>
</tbody></table>
<p> </p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-program-loop-structure/" data-id="clatcwb89004oxcq633shctkw" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-with-structure" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-with-structure/" class="article-date">
  <time datetime="2022-11-23T08:00:48.478Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-with-structure/">VBA With 结构</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>VBA 中，With 结构用于组合同一个对象的多个属性和方法，避免重复写同一个对象名，提高编程和运行效率。</p>
<h2 id="With-结构语法"><a href="#With-结构语法" class="headerlink" title="With 结构语法"></a>With 结构语法</h2><p><code>With</code> 结构由 <code>With</code> 和 <code>End With</code> 两个语句构成，对象的属性和方法都写在两者之间。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">With [对象]</span><br><span class="line">    .[属性] = [数据]</span><br><span class="line">    .[方法]</span><br><span class="line">    &#x27;其他属性和方法</span><br><span class="line">End With</span><br></pre></td></tr></table></figure>

<p><code>With</code> 结构里，对象的属性和方法均由<code>点 (.)</code>符号开始，后接对象的属性名和方法名。</p>
<h2 id="With-结构实例"><a href="#With-结构实例" class="headerlink" title="With 结构实例"></a>With 结构实例</h2><p>现在看一个实际的例子，需要将工作簿中 Sheet1 工作表设置新名称，然后设置标签颜色为黑色，最后隐藏工作表。</p>
<p>如果不用 With 结构，代码如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    Worksheets(&quot;Sheet1&quot;).Name = &quot;新名称&quot;</span><br><span class="line">    Worksheets(&quot;新名称&quot;).Tab.ThemeColor = xlThemeColorLight1</span><br><span class="line">    Worksheets(&quot;新名称&quot;).Visible = xlSheetHidden</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>可以看到，每个语句都重复写 <code>Worksheets(&quot;工作表名称&quot;)</code> 部分。</p>
<p>使用 With 结构，可以避免重复写同一个对象名，代码如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    With Worksheets(&quot;Sheet1&quot;)</span><br><span class="line">        .Name = &quot;新名称&quot;</span><br><span class="line">        .Tab.ThemeColor = xlThemeColorLight1</span><br><span class="line">        .Visible = xlSheetHidden</span><br><span class="line">    End With</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<h2 id="嵌套-With-结构"><a href="#嵌套-With-结构" class="headerlink" title="嵌套 With 结构"></a>嵌套 With 结构</h2><p>With 结构还能嵌套编写，即一个 With 结构中，如果父对象的属性是另一个对象，则针对这个子对象，继续使用 With 结构。</p>
<p>在之前的例子中，如果需要将 Sheet1 工作表中，A1:A10 单元格区域设置背景颜色，调整字体和字体大小，可以使用如下代码：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    With Worksheets(&quot;Sheet1&quot;)</span><br><span class="line">        .Name = &quot;新名称&quot;</span><br><span class="line">        .Tab.ThemeColor = xlThemeColorLight1</span><br><span class="line">        .Visible = xlSheetHidden</span><br><span class="line">        </span><br><span class="line">        With .Range(&quot;A1:A10&quot;)</span><br><span class="line">            .Interior.ThemeColor = xlThemeColorAccent1</span><br><span class="line">            .Font.Size = 12</span><br><span class="line">            .Font.Name = &quot;等线&quot;</span><br><span class="line">        End With</span><br><span class="line">        </span><br><span class="line">    End With</span><br><span class="line">    </span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>本篇我们学习了 VBA 程序结构中的 With 结构。With 结构可以将同一个对象的多个属性和方法组合起来，避免重复写对象名。此外，With 结构还能嵌套使用，进一步提高编程效率和程序运行效率。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-with-structure/" data-id="clatcwb8b004qxcq6gi685mpx" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-comment" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-comment/" class="article-date">
  <time datetime="2022-11-23T08:00:48.463Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-comment/">VBA 注释教程和实例</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>VBA 中，注释是对程序作出的说明。VBA 代码运行时，会跳过注释语句，因此注释不影响代码的运行。</p>
<h2 id="如何写注释"><a href="#如何写注释" class="headerlink" title="如何写注释"></a>如何写注释</h2><p>VBA 中的注释语句是，以英文单引号 <code>(&#39;)</code>开头，后接需要解释说明的内容。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">&#x27;注释的内容</span><br></pre></td></tr></table></figure>
<p>我们看实际的例子。</p>
<h2 id="注释可以写在一行，单引号后的内容均是注释的内容。注释也可以在一行代码的结尾，同样，单引号后的内容均是注释的内容。注释内容是多行内容，需要在每一行开头使用单引号。注释的用途"><a href="#注释可以写在一行，单引号后的内容均是注释的内容。注释也可以在一行代码的结尾，同样，单引号后的内容均是注释的内容。注释内容是多行内容，需要在每一行开头使用单引号。注释的用途" class="headerlink" title="注释可以写在一行，单引号后的内容均是注释的内容。注释也可以在一行代码的结尾，同样，单引号后的内容均是注释的内容。注释内容是多行内容，需要在每一行开头使用单引号。注释的用途"></a>注释可以写在一行，单引号后的内容均是注释的内容。<br><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    &#x27;定义姓名变量</span><br><span class="line">    Dim name As String</span><br><span class="line"></span><br><span class="line">End Sub</span><br><span class="line"></span><br></pre></td></tr></table></figure><br>注释也可以在一行代码的结尾，同样，单引号后的内容均是注释的内容。<br><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    &#x27;定义姓名变量</span><br><span class="line">    Dim name As String</span><br><span class="line">    </span><br><span class="line">    name = Range(&quot;A1&quot;).Value &#x27;从A1单元格读取姓名</span><br><span class="line"></span><br><span class="line">End Sub</span><br><span class="line"></span><br></pre></td></tr></table></figure><br>注释内容是多行内容，需要在每一行开头使用单引号。<br><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">&#x27;此过程用于读取 A1 单元格的值</span><br><span class="line">&#x27;最后更新于 2018-12-13</span><br><span class="line">&#x27;作者 一起学Excel</span><br><span class="line"></span><br><span class="line">Sub MyCode()</span><br><span class="line"></span><br><span class="line">    &#x27;定义姓名变量</span><br><span class="line">    Dim name As String</span><br><span class="line">    </span><br><span class="line">    name = Range(&quot;A1&quot;).Value &#x27;从A1单元格读取姓名</span><br><span class="line"></span><br><span class="line">End Sub</span><br><span class="line"></span><br></pre></td></tr></table></figure><br>注释的用途</h2><p>最基本的用途当然是对代码标注说明，例如：</p>
<ul>
<li>提供过程或函数的基本信息、用途；</li>
<li>说明变量的用途；</li>
<li>解释为什么使用当前的方法；</li>
<li>区分开不同代码块</li>
</ul>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_37fb07e5453dfbdaa22dc1e68f3e0694.png"></p>
<p>注释另一个常见的用法是，在开发调试过程中，临时注释一段代码，使其不被执行，检查代码其余部分是否有错误。</p>
<p><img src="https://university-oss-2022.oss-cn-guangzhou.aliyuncs.com/easyexcel/uploads/im_68987c2a8d0ee030d5f7b9ff1fe64992.png"></p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>注释是程序编写中最基础也是最重要的一部分。</p>
<p>建议大家养成写注释的习惯。因为实际开发过程中，程序中会有很多变量、语句，如果稍不注意就会搞混代码，需要从头开始对，浪费时间。更不用说过一段时间再查看代码，或者代码给其他人看。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-comment/" data-id="clatcwb7w004ixcq66by25m98" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  
    <article id="post-vba-subroutine-basics" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/study-excel/vba-subroutine-basics/" class="article-date">
  <time datetime="2022-11-23T08:00:48.463Z" itemprop="datePublished">2022-11-23</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/study-excel/categories/VBA/">VBA</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/study-excel/vba-subroutine-basics/">VBA 过程(Sub) 入门教程和实例</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>学习完 <a href="/study-excel/vba/basic-excel-vba/">Excel VBA 基础</a>、<a href="/study-excel/vba/vba-variable-operators/">VBA 变量</a>以及<a href="/study-excel/vba/vba-program-structure/">程序结构</a>，在本章《VBA 过程和函数》，将学习 VBA 中的另一个基础又核心的概念，过程(Sub)。</p>
<p>在今天的这篇文章，先学习 VBA 过程的基础，包括如何写、传入参数、执行和调用过程等。</p>
<h2 id="写在前面"><a href="#写在前面" class="headerlink" title="写在前面"></a>写在前面</h2><p>VBA 中，过程是一切的开始，几乎所有的代码，都会被写在一个或多个过程里。</p>
<p>实际开发中，通常一个过程，建议只完成一个特定的小目标。因此，我们的程序往往会包含多个过程。这就是 VBA 中过程概念存在的一个原因。</p>
<p>程序中使用过程，可以使程序更简洁、清晰，开发中大型项目更易于管理代码。</p>
<h2 id="过程基础语法"><a href="#过程基础语法" class="headerlink" title="过程基础语法"></a>过程基础语法</h2><p>VBA 过程以 <code>Sub</code> 语句开始，以 <code>End Sub</code> 语句结束，包含一个或多个语句，完成一个特定的目标。</p>
<h3 id="无参数过程"><a href="#无参数过程" class="headerlink" title="无参数过程"></a>无参数过程</h3><p>无参数的 VBA 过程的基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">Sub [过程名]()</span><br><span class="line">    语句1</span><br><span class="line">    语句2</span><br><span class="line">    ...</span><br><span class="line">    语句n</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>可以看到，过程以 <code>Sub</code> 语句开始，以 <code>End Sub</code> 语句结束，并且具备一个名称，名称后有括号 <code>()</code>。</p>
<p>我们看一个简单的例子。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Sub SayHello()</span><br><span class="line">    Msgbox &quot;Hello World&quot;</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>上述就是一个简单的过程，过程名是 <code>SayHello</code>。这个过程只包含一个语句，运行时，弹出对话框显示 <code>Hello World</code>。</p>
<h3 id="有参数过程"><a href="#有参数过程" class="headerlink" title="有参数过程"></a>有参数过程</h3><p>过程还可以接受一个或多个参数，参数可以是常量、变量、表达式，并且每个参数指定其名称。在过程的语句中，接受的参数，以名称指定方式被使用。</p>
<p>接受参数的过程基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">Sub [过程名]([变量名1] As [数据类型1],...[变量名n] As [数据类型n])</span><br><span class="line">    语句1</span><br><span class="line">    语句2</span><br><span class="line">    ...</span><br><span class="line">    语句n</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>与无参数过程相比，有参数过程在过程名后的括号 <code>()</code> 中，包含一个或多个参数。参数的写法与声明变量语句类似，不同点是在这里不用写 <code>Dim</code>。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[变量名1] As [数据类型1]</span><br></pre></td></tr></table></figure>

<p>我们看一个例子。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">&#x27;声明一个过程</span><br><span class="line">Sub SayHello(name As String)</span><br><span class="line">    Msgbox &quot;Hello&quot; &amp; name</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">&#x27;在另一个过程，调用上述过程，调用时，提供一个实际的 name 参数</span><br><span class="line">Sub MyCode()</span><br><span class="line">    SayHello &quot;World 2&quot;</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>我们在运行 <code>MyCode</code> 过程时，提供了 <code>name</code> 变量，即 <code>World 2</code> ，运行时弹出对话框显示 <code>Hello World 2</code>。</p>
<h2 id="调用子过程（Sub）"><a href="#调用子过程（Sub）" class="headerlink" title="调用子过程（Sub）"></a>调用子过程（Sub）</h2><p>在程序开发中，把代码拆分成多个子过程和函数，可以使项目更容易管理、测试和运行，VBA 中也不例外。</p>
<p>实际开发中，项目通常具备一个主入口过程，或称为父过程。父过程通过调用多个子过程和函数，完成一系列复杂的操作。其中子过程和函数一般只负责一个操作或动作。</p>
<p>下面看一个简单的例子。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">&#x27;主入口</span><br><span class="line">Sub Main()</span><br><span class="line">    Dim name As String</span><br><span class="line">    Dim title As String</span><br><span class="line">    </span><br><span class="line">    name = &quot;Zhang san&quot;</span><br><span class="line">    title = &quot;CEO&quot;</span><br><span class="line">    </span><br><span class="line">    WriteInfo name &amp; &quot;,&quot; &amp; title</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">&#x27;子过程，在工作表A1单元格填写信息</span><br><span class="line">Sub WriteInfo(info As String)</span><br><span class="line">    Range(&quot;A1&quot;) = info</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>以上的例子中，<code>Main</code> 过程是一个主入口（父过程），程序从此处开始执行，先是给 <code>name</code> 和 <code>title</code> 变量赋值，最后调用 <code>WriteInfo</code> 子过程，将两个信息合并后写到工作表上的 A1 单元格。</p>
<p>接下来介绍调用子过程和函数的基本语法。</p>
<p>调用子过程有两种方法，<strong>直接调用</strong>和<strong>使用 Call 关键词调用</strong>。两种方法对子过程的参数有不同的要求。</p>
<h3 id="直接调用"><a href="#直接调用" class="headerlink" title="直接调用"></a>直接调用</h3><p>直接调用，直接写过程名，即可调用过程。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">Sub Main()</span><br><span class="line">    MySub</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">Sub MySub()</span><br><span class="line">    &#x27;代码</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>如果子过程需要输入参数，多个参数只需用逗号（,）分开即可。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">Sub Main()</span><br><span class="line">    MySub 2019,&quot;年&quot;</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">Sub MySub(val1 As Integer, val2 As String)</span><br><span class="line">    &#x27;代码</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p> </p>
<h3 id="使用关键词-Call-调用"><a href="#使用关键词-Call-调用" class="headerlink" title="使用关键词 Call 调用"></a>使用关键词 Call 调用</h3><p>使用 Call 关键词调用时，Call 后接过程名。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">Sub Main()</span><br><span class="line">    Call MySub</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">Sub MySub()</span><br><span class="line">    &#x27;代码</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>

<p>如果子过程需要输入参数，则<strong>需要将参数放在括号内</strong>。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">Sub Main()</span><br><span class="line">    Call MySub(2019,&quot;年&quot;)</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">Sub MySub(val1 As Integer, val2 As String)</span><br><span class="line">    &#x27;代码</span><br><span class="line">End Sub</span><br></pre></td></tr></table></figure>


<blockquote>
<p>注：程序角度看，调用过程时，不需要使用 Call 关键字，因此不建议此种方法。</p>
</blockquote>
<h2 id="提前退出过程"><a href="#提前退出过程" class="headerlink" title="提前退出过程"></a>提前退出过程</h2><p>正常情况下，VBA 过程以 <code>Sub</code> 语句开始，以 <code>End Sub</code> 语句结束。但有时根据实际情况，可能需要提前结束并退出过程。VBA 提供 2 种提前退出过程的方法，<code>Exit Sub</code> 和 <code>End</code> 方法。</p>
<h3 id="Exit-Sub-语句"><a href="#Exit-Sub-语句" class="headerlink" title="Exit Sub 语句"></a>Exit Sub 语句</h3><p>在一个过程中，当程序运行到 <code>Exit Sub</code> 语句时，立即结束<strong>当前过程</strong>，提前退出。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">Sub Main()</span><br><span class="line">    Call MySub</span><br><span class="line">    Msgbox &quot;父过程&quot;</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">Sub MySub()</span><br><span class="line">    Exit Sub</span><br><span class="line">    Msgbox &quot;子过程&quot;</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">&#x27;运行 Main 过程，返回结果：</span><br><span class="line">=&gt; &quot;父过程&quot;</span><br></pre></td></tr></table></figure>

<p>在以上例子中，<code>Main</code> 过程调用 <code>MySub</code> 子过程，遇到 <code>Exit Sub</code> 语句，立即退出子过程，回到父过程 <code>Main</code> ，继续运行余下的语句。</p>
<p><strong>这里需要注意的是，<code>Exit Sub</code> 语句只作用于当前过程，不影响调用它的父过程。</strong></p>
<h3 id="End-语句"><a href="#End-语句" class="headerlink" title="End 语句"></a>End 语句</h3><p>在一个过程，当程序运行到 <code>End</code> 语句时，立即<strong>结束当前运行的所有 VBA 过程</strong>。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">Sub Main()</span><br><span class="line">    Call MySub</span><br><span class="line">    Msgbox &quot;父过程&quot;</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">Sub MySub()</span><br><span class="line">	End</span><br><span class="line">    Msgbox &quot;子过程&quot;</span><br><span class="line">End Sub</span><br><span class="line"></span><br><span class="line">&#x27;运行 Main 过程，返回结果：</span><br><span class="line">=&gt; 无返回结果</span><br></pre></td></tr></table></figure>

<p>在以上例子中，<code>Main</code> 过程调用 <code>MySub</code> 子过程，遇到 <code>End</code> 语句时，立即结束当前运行的所有过程，包括父过程 <code>Main</code>。</p>
<p>在实际开发中，应谨慎使用 <code>End</code> 结束语句。<code>End</code> 语句的效果类似于电脑的强制关机命令，立即结束所有程序，不会保存任何值，于 VBA 有以下效果：</p>
<ul>
<li>程序中对象的各类事件不会被触发；</li>
<li>任何在运行的 VBA 程序都会停止；</li>
<li>对象引用都会失效；</li>
<li>任何打开的窗体都被关闭。</li>
</ul>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h2><p>过程是 VBA 的一个核心概念，几乎所有的代码会写在一个或多个过程里。过程可以接受 0 个或多个参数，参数可以在过程或函数里使用。在过程中可以调用其他子过程，把复杂的代码分成若干个过程，使代码易于管理和编写。最后过程可以提前结束，做到不需要运行所有的语句就退出过程。</p>
<p> </p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://lecoder.gitee.io/study-excel/vba-subroutine-basics/" data-id="clatcwb8d004sxcq6abfk2rmu" class="article-share-link">Share</a>
      
      
    </footer>
  </div>
  
</article>


  


  <nav id="page-nav">
    
    <a class="extend prev" rel="prev" href="/study-excel/page/64/">&amp;laquo; 上一页</a><a class="page-number" href="/study-excel/">1</a><span class="space">&hellip;</span><a class="page-number" href="/study-excel/page/63/">63</a><a class="page-number" href="/study-excel/page/64/">64</a><span class="page-number current">65</span><a class="page-number" href="/study-excel/page/66/">66</a><a class="page-number" href="/study-excel/page/67/">67</a><span class="space">&hellip;</span><a class="page-number" href="/study-excel/page/94/">94</a><a class="extend next" rel="next" href="/study-excel/page/66/">下一页 &amp;raquo;</a>
  </nav>

</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/VBA/">VBA</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%85%AC%E5%BC%8F/">公式</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%87%BD%E6%95%B0/">函数</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/">函数与公式基础</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/%E5%9F%BA%E7%A1%80/">基础</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/%E5%9F%BA%E7%A1%80/%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/">公式基础</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/%E5%9F%BA%E7%A1%80/%E5%85%AC%E5%BC%8F%E9%94%99%E8%AF%AF/">公式错误</a></li></ul></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%8A%9F%E8%83%BD/">功能</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%8A%9F%E8%83%BD%E5%8C%BA/">功能区</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%8A%9F%E8%83%BD%E5%8C%BA/%E5%9F%BA%E7%A1%80/">基础</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%8A%9F%E8%83%BD%E5%8C%BA/%E5%9F%BA%E7%A1%80/%E5%85%AC%E5%BC%8F/">公式</a></li></ul></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%8D%95%E5%85%83%E6%A0%BC%E5%BC%95%E7%94%A8/">单元格引用</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%8D%95%E5%85%83%E6%A0%BC%E5%BC%95%E7%94%A8/%E5%9F%BA%E7%A1%80/">基础</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%8D%95%E5%85%83%E6%A0%BC%E5%BC%95%E7%94%A8/%E5%9F%BA%E7%A1%80/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/">函数与公式基础</a></li></ul></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/">基本概念</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/%E5%9F%BA%E7%A1%80/">基础</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/%E5%9F%BA%E7%A1%80/%E4%BD%BF%E7%94%A8-Excel/">使用 Excel</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/%E5%9F%BA%E7%A1%80/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F/">函数与公式</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/%E5%9F%BA%E7%A1%80/%E5%8D%95%E5%85%83%E6%A0%BC/">单元格</a></li></ul></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/">基本设置</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/%E5%9F%BA%E7%A1%80/">基础</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/%E5%9F%BA%E7%A1%80/%E4%BF%9D%E5%AD%98%E8%AE%BE%E7%BD%AE/">保存设置</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/%E5%9F%BA%E7%A1%80/%E5%85%AC%E5%BC%8F%E8%AE%BE%E7%BD%AE/">公式设置</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/%E5%9F%BA%E7%A1%80/%E5%85%B6%E4%BB%96%E8%AE%BE%E7%BD%AE/">其他设置</a></li></ul></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/">基础</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/">基础操作</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E4%BF%9D%E6%8A%A4/">保护</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%A4%8D%E5%88%B6%E5%85%AC%E5%BC%8F/">复制公式</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%A4%8D%E5%88%B6%E5%85%AC%E5%BC%8F/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/">函数与公式基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%AE%A1%E9%98%85/">审阅</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%AE%A1%E9%98%85/%E5%8A%9F%E8%83%BD%E5%8C%BA/">功能区</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%B7%A5%E4%BD%9C%E7%95%8C%E9%9D%A2/">工作界面</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%B7%A5%E4%BD%9C%E7%95%8C%E9%9D%A2/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/">基本概念</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%B8%B8%E8%A7%81%E6%9C%AF%E8%AF%AD/">常见术语</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%B8%B8%E8%A7%81%E6%9C%AF%E8%AF%AD/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/">基本概念</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%B8%B8%E8%A7%84%E8%AE%BE%E7%BD%AE/">常规设置</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%B8%B8%E8%A7%84%E8%AE%BE%E7%BD%AE/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/">基本设置</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%BC%80%E5%A7%8B/">开始</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E5%BC%80%E5%A7%8B/%E5%8A%9F%E8%83%BD%E5%8C%BA/">功能区</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%8F%92%E5%85%A5/">插入</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%8F%92%E5%85%A5/%E5%8A%9F%E8%83%BD%E5%8C%BA/">功能区</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%95%B0%E6%8D%AE/">数据</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%95%B0%E6%8D%AE/%E5%8A%9F%E8%83%BD%E5%8C%BA/">功能区</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%96%B0%E5%A2%9E%E5%8A%9F%E8%83%BD%E5%92%8C%E5%87%BD%E6%95%B0/">新增功能和函数</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%96%B0%E5%A2%9E%E5%8A%9F%E8%83%BD%E5%92%8C%E5%87%BD%E6%95%B0/Excel-2007/">Excel 2007</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%96%B0%E5%A2%9E%E5%8A%9F%E8%83%BD%E5%92%8C%E5%87%BD%E6%95%B0/Excel-2010/">Excel 2010</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%96%B0%E5%A2%9E%E5%8A%9F%E8%83%BD%E5%92%8C%E5%87%BD%E6%95%B0/Excel-2013/">Excel 2013</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%96%B0%E5%A2%9E%E5%8A%9F%E8%83%BD%E5%92%8C%E5%87%BD%E6%95%B0/Excel-2016/">Excel 2016</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%A0%A1%E5%AF%B9%E8%AE%BE%E7%BD%AE/">校对设置</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E6%A0%A1%E5%AF%B9%E8%AE%BE%E7%BD%AE/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/">基本设置</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E7%BC%96%E5%86%99%E5%85%AC%E5%BC%8F/">编写公式</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E7%BC%96%E5%86%99%E5%85%AC%E5%BC%8F/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/">函数与公式基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E7%BC%96%E8%BE%91%E5%85%AC%E5%BC%8F/">编辑公式</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E7%BC%96%E8%BE%91%E5%85%AC%E5%BC%8F/%E5%87%BD%E6%95%B0%E4%B8%8E%E5%85%AC%E5%BC%8F%E5%9F%BA%E7%A1%80/">函数与公式基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E8%A7%86%E5%9B%BE/">视图</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E8%A7%86%E5%9B%BE/%E5%8A%9F%E8%83%BD%E5%8C%BA/">功能区</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E9%A1%B5%E9%9D%A2%E5%B8%83%E5%B1%80/">页面布局</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E9%A1%B5%E9%9D%A2%E5%B8%83%E5%B1%80/%E5%8A%9F%E8%83%BD%E5%8C%BA/">功能区</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E9%AB%98%E7%BA%A7%E8%AE%BE%E7%BD%AE/">高级设置</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E9%AB%98%E7%BA%A7%E8%AE%BE%E7%BD%AE/%E5%9F%BA%E6%9C%AC%E8%AE%BE%E7%BD%AE/">基本设置</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E9%BC%A0%E6%A0%87%E6%8C%87%E9%92%88/">鼠标指针</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80/%E9%BC%A0%E6%A0%87%E6%8C%87%E9%92%88/%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5/">基本概念</a></li></ul></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/">基础操作</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E5%A4%8D%E5%88%B6%E4%B8%8E%E7%B2%98%E8%B4%B4/">复制与粘贴</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E5%A4%8D%E5%88%B6%E4%B8%8E%E7%B2%98%E8%B4%B4/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E6%89%93%E5%8D%B0/">打印</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E6%89%93%E5%8D%B0/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E6%9F%A5%E6%89%BE%E4%B8%8E%E9%80%89%E6%8B%A9/">查找与选择</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E6%9F%A5%E6%89%BE%E4%B8%8E%E9%80%89%E6%8B%A9/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E5%8D%95%E5%85%83%E6%A0%BC%E6%A0%BC%E5%BC%8F/">编辑单元格格式</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E5%8D%95%E5%85%83%E6%A0%BC%E6%A0%BC%E5%BC%8F/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E5%B7%A5%E4%BD%9C%E8%A1%A8/">编辑工作表</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E5%B7%A5%E4%BD%9C%E8%A1%A8/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E5%B7%A5%E4%BD%9C%E8%A1%A8%E5%8C%BA%E5%9F%9F/">编辑工作表区域</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E5%B7%A5%E4%BD%9C%E8%A1%A8%E5%8C%BA%E5%9F%9F/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E6%95%B0%E5%AD%97%E6%A0%BC%E5%BC%8F/">编辑数字格式</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E7%BC%96%E8%BE%91%E6%95%B0%E5%AD%97%E6%A0%BC%E5%BC%8F/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E9%BC%A0%E6%A0%87%E6%93%8D%E4%BD%9C/">鼠标操作</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/%E9%BC%A0%E6%A0%87%E6%93%8D%E4%BD%9C/%E5%9F%BA%E7%A1%80/">基础</a></li></ul></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E5%BF%AB%E6%8D%B7%E9%94%AE/">快捷键</a></li><li class="category-list-item"><a class="category-list-link" href="/study-excel/categories/%E9%97%AE%E9%A2%98/">问题</a></li></ul>
    </div>
  </div>


  
    

  
    
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/study-excel/archives/2022/11/">十一月 2022</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/study-excel/excel-coupdaybs-function/">Excel COUPDAYBS 函数</a>
          </li>
        
          <li>
            <a href="/study-excel/excel-coupdays-function/">Excel COUPDAYS 函数</a>
          </li>
        
          <li>
            <a href="/study-excel/excel-coupdaysnc-function/">Excel COUPDAYSNC 函数</a>
          </li>
        
          <li>
            <a href="/study-excel/excel-coupncd-function/">Excel COUPNCD 函数</a>
          </li>
        
          <li>
            <a href="/study-excel/excel-coupnum-function/">Excel COUPNUM 函数</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2022 lijile<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/study-excel/" class="mobile-nav-link">Home</a>
  
    <a href="/study-excel/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  
<link rel="stylesheet" href="/study-excel/fancybox/jquery.fancybox.css">

  
<script src="/study-excel/fancybox/jquery.fancybox.pack.js"></script>




<script src="/study-excel/js/script.js"></script>




  </div>
</body>
</html>